USE <database_name>;
GO

SELECT * FROM sys.database_query_store_options;

USE master;
GO

-- enable query store with settings we want
ALTER DATABASE <database_name> 
	SET QUERY_STORE = ON;
GO

ALTER DATABASE <database_name> SET QUERY_STORE (
	OPERATION_MODE = READ_WRITE, 
	CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
	DATA_FLUSH_INTERVAL_SECONDS = 3000,
	INTERVAL_LENGTH_MINUTES = 15,
	MAX_STORAGE_SIZE_MB = 500, 
	QUERY_CAPTURE_MODE = AUTO,
	SIZE_BASED_CLEANUP_MODE = AUTO, 
	MAX_PLANS_PER_QUERY = 1000);
GO

-- clear out any old data, just in case
ALTER DATABASE <database_name> 
	SET QUERY_STORE CLEAR;
GO

-- flush QS data to disk
EXECUTE sys.sp_query_store_flush_db;
GO

-- query store can silently change operations mode
/*
the difference between the actual_state_desc and desired_state_desc indicates
that a change of operations mode occurred automatically
*/
SELECT
	actual_state_desc,
	desired_state_desc,
	current_storage_size_mb,   
    max_storage_size_mb,
	readonly_reason,
	interval_length_minutes,   
    stale_query_threshold_days,
	size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  
GO

-- find a query
SELECT 
    qst.query_text_id,
	qsq.query_id,  
	qst.query_sql_text, 
	qsp.compatibility_level,
	qsp.engine_version,
	qsp.plan_id, 
    rs.last_execution_time,
	(DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())), 
	rs.last_execution_time)) AS local_last_execution_time
FROM sys.query_store_query qsq 
INNER JOIN sys.query_store_query_text qst
	ON qsq.query_text_id = qst.query_text_id
INNER JOIN sys.query_store_plan qsp 
	ON qsq.query_id = qsp.query_id
INNER JOIN sys.query_store_runtime_stats rs 
	ON qsp.plan_id = rs.plan_id 
-- WHERE qsp.compatibility_level < 120; 
GO

-- use the query_text_id to pinpoint problematic queries
SELECT 
    qst.query_text_id,
	qsq.query_id,  
	qsp.compatibility_level,
	qsp.engine_version,
	qsp.plan_id, 
	rs.avg_cpu_time,
	rs.avg_logical_io_reads,
	rs.avg_duration,
	TRY_CONVERT(XML, qsp.query_plan) AS query_plan,
	qst.query_sql_text
FROM sys.query_store_query qsq 
JOIN sys.query_store_query_text qst
	ON qsq.query_text_id = qst.query_text_id
JOIN sys.query_store_plan qsp 
	ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats rs 
	ON qsp.plan_id = rs.plan_id 
WHERE qst.query_text_id = <query_text_id>;
GO

-- cleanup
USE master;
GO

ALTER DATABASE <database_name> 
	SET QUERY_STORE = OFF;
GO